/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.almori.hr.timetracker;

import java.io.BufferedReader; 
import java.io.File; 
import java.io.FileNotFoundException; 
import java.io.FileReader; 
import java.io.IOException; 
import java.io.Reader; 
import java.util.StringTokenizer; 

import java.sql.*;
import java.util.Properties;  
import java.io.*; 

/**
 *
 * @author gcastillo, nbenavidez
 */

public class CSVtoOracleHandler 
{ 
         private static final String PROP_FILE="DBConfig.properties";
         public static void main(String[] args) throws  SQLException,java.lang.Exception 
         { 
          
              String fileName="C:/pop.csv"; 
              
              try 
              { 
                  BufferedReader br = new BufferedReader(new FileReader(fileName)); 
                  String strLine = null; 
                  StringTokenizer st = null; 
                  int lineNumber = 0, tokenNumber = 0; 

                  while( (fileName = br.readLine()) != null) 
                  { 
                      lineNumber++; //break comma separated line using "," 
                      st = new StringTokenizer(fileName, ","); 

                      while(st.hasMoreTokens()) 
                      { //display csv values 
                           tokenNumber++; 
                           String firstname,lastname,ced,pass;
                           firstname = st.nextToken();
                           lastname =  st.nextToken();
                           ced = st.nextToken();
                           pass = st.nextToken();
                           System.out.println("'" + firstname + "','"+ lastname  + "','"+ ced + "','" + pass + "'"); 
                           
                            boolean executeResult = false;
                            Connection conn = getConnection();
                       //     conn.setAutoCommit(true);
                            Statement ste = conn.createStatement();
                            ste = conn.createStatement();
                            String sql = "INSERT INTO EMPLOYEES (ID,FIRSTNAME,SECONDNAME,LASTNAME,CED,STATUS,ID_DEPT,PASS,PIC ) values (" + lineNumber + ",'" + firstname + "',' ','" + lastname + "','"+ ced +"','OUT',1,'" + pass + "','" + ced +".jpg')";
                            executeResult = ste.execute(sql);
                            
                            conn.close();
                            ste.close();
                          
                      } //reset token number 
                      tokenNumber = 0; 
                  } 
                  System.out.println(lineNumber);
               } 
               catch (FileNotFoundException e) 
               { 
                   e.printStackTrace(); 
               } catch (IOException e) { 
                   e.printStackTrace(); 
               } 
          } 
       public static Connection getConnection() throws Exception {
       try{  
         InputStream is = TimeTrackerDAO.class.getResourceAsStream(PROP_FILE);  
         Properties prop = new Properties();  
         prop.load(is);  
         String driver = prop.getProperty("driver");  
         String url = prop.getProperty("url");  
         String username = prop.getProperty("username"); 
         String password = prop.getProperty("password");
         is.close();  
         Class.forName(driver);
         return DriverManager.getConnection(url, username, password);
      /* code to use values read from the file*/  
       }catch(Exception e){  
         System.out.println("Failed to read from " + PROP_FILE + " file." + e); 
         return null;
       }  
      
    
  }
}  